﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DevExpress.XtraTreeList;
using ProgNetComponents.Data;
using ProgNetComponents.Data.SqlCe;
using System.Data.SqlServerCe;
using System.Data;
using System.Windows.Forms;
using ProgNetComponentsDx.Options;
using ProgNetComponents.Collections;
using ProgNetComponents.Interfaces;

namespace SqlCeManager.Tools
{
    public class RootNode : TreeList.IVirtualTreeListData
    {
        List<ConnectionNode> connections ;
        public RootNode(List<ConnectionNode> list)
        {
            connections = list;
        }

        #region IVirtualTreeListData Members

        public void VirtualTreeGetCellValue(VirtualTreeGetCellValueInfo info)
        {
            info.CellData = "Connections";
        }

        public void VirtualTreeGetChildNodes(VirtualTreeGetChildNodesInfo info)
        {
            info.Children = connections;
        }

        public void VirtualTreeSetCellValue(VirtualTreeSetCellValueInfo info)
        {
           
        }

        #endregion
    }

    public class ConnectionNode : INamedObject, TreeList.IVirtualTreeListData
    {
        public int IdPolaczenia
        {
            get;
            set;
        }
        private SqlCeDataManager PrivateManager = new SqlCeDataManager();
        private SqlCeDataManager _Manager = null;
        private SqlCeDataManager GetManager(string connectionString)
        {
            if (_Manager == null)
            {
                _Manager = new SqlCeDataManager();
                _Manager.UseGlobalConnectionString = false;
            }
            if (_Manager.ConnectionString != connectionString)
            {
                _Manager.ConnectionString = connectionString;
                _Manager.SchemaChecker.RefreshSchema();
                if (!_Manager.SchemaChecker.ColumnExists("DB_UPDATE_SCRIPTS", "DB_UPDS_ID"))
                {
                    string str = "CREATE TABLE DB_UPDATE_SCRIPTS( " +
                                    "DB_UPDS_ID int IDENTITY (1, 1) NOT NULL, " +
                                    "DB_UPDS_SCRIPT nvarchar(4000) NOT NULL, " +
                                    "DB_UPDS_SCRIPT_SHORT nvarchar(100) NOT NULL, " +
                                    "DB_UPDS_EXECUTED bit NOT NULL, " +
                                    "DB_UPDS_ERROR nvarchar(250) " +
                                    "); ";
                    _Manager.ExecuteNonQuery(str);

                    str = "ALTER TABLE DB_UPDATE_SCRIPTS ADD CONSTRAINT " +
                                    "DB_UPDATE_SCRIPTS_PK PRIMARY KEY (DB_UPDS_ID); ";
                    _Manager.ExecuteNonQuery(str);
                    _Manager.SchemaChecker.RefreshSchema();
                }
            }
            return _Manager;
        }

        private string _ConnectionString;
        private List<Form> _OwnedForms = new List<Form>();
        public List<Form> OwnedForms
        {
            get { return _OwnedForms; }
        }

        private SchemaDataSet _SchemaDataSet = new SchemaDataSet();
        public SchemaDataSet SchemaDataSet
        {
            get { return _SchemaDataSet; }
        }
        private ScriptTools _ScriptTools = null;
        public ScriptTools ScriptTools
        {
            get { return _ScriptTools; }
        }

        private string _Name;
        public string Name
        {
            get { return _Name; }
            set { _Name = value; }
        }
        private TreeList tree;
        public ConnectionNode(string name, string connectionString, TreeList tree)
        {
            _ConnectionString = connectionString;
            _Name = name;
            _Adapter = new SqlCeDataAdapter("", connectionString);
            this.tree = tree;
            GetShema();
            _ScriptTools = new ScriptTools(SchemaDataSet);
        }

        public string ConnectionString
        {
            get { return _ConnectionString; }
        }

        private SqlCeDataAdapter _Adapter = null;
        public SqlCeDataAdapter Adapter
        {
            get
            {
                return _Adapter;
            }
        }

        private void GetShema()
        {
            GetTable(null);
        }

        public void GetTable(string table)
        {
            if (table.IsNull())
            {
                _Tables.Clear();
                _SchemaDataSet.Clear();
            }
            else
            {
                table = table.ToUpper();
                _Tables.Remove(table);
                _SchemaDataSet.TABLES.DeleteRows("TABLE_NAME = " + table.EscapeSql());
            }
            Adapter.SelectCommand.CommandText = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES";
            if (table.IsNotNull())
                Adapter.SelectCommand.CommandText += " WHERE TABLE_NAME = " + table.EscapeSql();
            try
            {
                Adapter.Fill(_SchemaDataSet.TABLES);
            }
            catch { }
            TableNode t = null;
            if (table.IsNotNull())
            {
                t = new TableNode(this, table.ToUpper());
                _Tables.Add(t);
            }
            else
            {
                foreach (DataRow r in _SchemaDataSet.TABLES)
                {
                    t = new TableNode(this, r["TABLE_NAME"].AsString().ToUpper());
                    _Tables.Add(t);
                }
            }
            GetColumns(table);
            GetFk(table);
            GetIndexes(table);
            if (Adapter.SelectCommand.Connection.State != ConnectionState.Closed)
                Adapter.SelectCommand.Connection.Close();
            Tables.Sort();
            if (tree != null)
                tree.RefreshDataSource();
        }
        private void GetColumns(string table)
        {
            if (table.IsNotNull())
                _SchemaDataSet.COLUMNS.DeleteRows("TABLE_NAME = " + table.EscapeSql());
            Adapter.SelectCommand.CommandText = "SELECT TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT, "
            + "IS_NULLABLE,DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, "
            + "AUTOINC_SEED, AUTOINC_INCREMENT FROM INFORMATION_SCHEMA.COLUMNS";
            if (table.IsNotNull())
                Adapter.SelectCommand.CommandText += " WHERE TABLE_NAME = " + table.EscapeSql();
            try
            {
                Adapter.Fill(_SchemaDataSet.COLUMNS);
            }
            catch { }

            string filter = null;
            if (table.IsNotNull())
                filter = "TABLE_NAME = " + table.EscapeSql();
            DataRow[] rows = _SchemaDataSet.COLUMNS.Select(filter, null, DataViewRowState.CurrentRows);
            foreach (DataRow r in rows)
            {
                if (r["COLUMN_DEFAULT"].IsNotNull())
                    r["COLUMN_DEFAULT"] = r["COLUMN_DEFAULT"].ToString().XReplace("''", "'").GetFirstMatch("^'(.*)'$", 1, System.Text.RegularExpressions.RegexOptions.Singleline);
                r.AcceptChanges();

                TableNode t = _Tables[r["TABLE_NAME"].AsString().ToUpper()];
                if (t != null)
                {
                    int size = -1;
                    int scale = -1;
                    int precision = -1;

                    string type = r["DATA_TYPE"].AsString();
                    if (type == "numeric")
                    {
                        scale = r["NUMERIC_SCALE"].AsInt();
                        precision = r["NUMERIC_PRECISION"].AsInt();
                    }
                    else if(ScriptTools.CanSetMaxLength(type))
                    {
                        size = r["CHARACTER_MAXIMUM_LENGTH"].AsInt();
                    }
                    t.Columns.Add(new ColumnNode(t, r["COLUMN_NAME"].AsString(), type,size,precision,scale, r["IS_NULLABLE"].AsString() == "YES"));
                }
            }
        }
        private void GetFk(string table)
        {
            if (table.IsNotNull())
                _SchemaDataSet.REFERENTIAL_CONSTRAINTS.DeleteRows("CONSTRAINT_TABLE_NAME = " + table.EscapeSql());
            Adapter.SelectCommand.CommandText = "SELECT DISTINCT CONSTRAINT_TABLE_NAME, CONSTRAINT_NAME, "
                + " UNIQUE_CONSTRAINT_TABLE_NAME, UPDATE_RULE, DELETE_RULE FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS";
            if (table.IsNotNull())
                Adapter.SelectCommand.CommandText += " WHERE CONSTRAINT_TABLE_NAME = " + table.EscapeSql();
            try
            {
                Adapter.Fill(_SchemaDataSet.REFERENTIAL_CONSTRAINTS);
            }
            catch { }
            GetFkColumns(table);
        }
        private void GetFkColumns(string table)
        {
            Adapter.SelectCommand.CommandText = "SELECT REF.CONSTRAINT_NAME, PCOLS.COLUMN_NAME AS PRIMARY_COLUMN, "
                 + " FCOLS.COLUMN_NAME AS FOREIGN_COLUMN FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS REF, "
                 + " INFORMATION_SCHEMA.KEY_COLUMN_USAGE PCOLS, INFORMATION_SCHEMA.KEY_COLUMN_USAGE FCOLS "
                 + " WHERE REF.CONSTRAINT_NAME = FCOLS.CONSTRAINT_NAME AND REF.UNIQUE_CONSTRAINT_NAME = PCOLS.CONSTRAINT_NAME "
                 + "AND FCOLS.ORDINAL_POSITION = PCOLS.ORDINAL_POSITION";
            if (table.IsNotNull())
                Adapter.SelectCommand.CommandText += " AND REF.CONSTRAINT_TABLE_NAME = " + table.EscapeSql();
            try
            {
                Adapter.Fill(_SchemaDataSet.REFERENTIAL_CONSTRAINTS_COLUMNS);
            }
            catch { }
        }

        private void GetIndexes(string table)
        {
            if (table.IsNotNull())
                _SchemaDataSet.INDEXES.DeleteRows("TABLE_NAME = " + table.EscapeSql());
            Adapter.SelectCommand.CommandText = "SELECT DISTINCT TABLE_NAME, INDEX_NAME, PRIMARY_KEY, [UNIQUE] FROM  INFORMATION_SCHEMA.INDEXES";
            if (table.IsNotNull())
                Adapter.SelectCommand.CommandText += " WHERE TABLE_NAME = " + table.EscapeSql();
            try
            {
                Adapter.Fill(_SchemaDataSet.INDEXES);
            }
            catch { }

            GetIndexesColumns(table);
        }
        private void GetIndexesColumns(string table)
        {
            Adapter.SelectCommand.CommandText = "SELECT INDEX_NAME, COLUMN_NAME FROM  INFORMATION_SCHEMA.INDEXES";
            if (table.IsNotNull())
                Adapter.SelectCommand.CommandText += " WHERE TABLE_NAME = " + table.EscapeSql();
            try
            {
                Adapter.Fill(_SchemaDataSet.INDEXES_COLUMNS);
            }
            catch { }

        }
        private TableNodeCollection _Tables = new TableNodeCollection();
        public TableNodeCollection Tables
        {
            get { return _Tables; }
        }
        #region IVirtualTreeListData Members

        public void VirtualTreeGetCellValue(VirtualTreeGetCellValueInfo info)
        {
            if(info.Column.FieldName == "NodeData")
            info.CellData = _Name;
        }

        public void VirtualTreeGetChildNodes(VirtualTreeGetChildNodesInfo info)
        {
            info.Children = _Tables;
        }

        public void VirtualTreeSetCellValue(VirtualTreeSetCellValueInfo info)
        {

        }

        #endregion

        private string _LastQuery = "";
        public string LastQuery
        {
            get { return _LastQuery; }
        }
        public bool ExecuteCommands(List<string> commands)
        {
            SqlCeConnection conn = null;
            SqlCeTransaction tr = null;
            SqlCeCommand cmd = null;
            bool result = true;

            try
            {
                _LastQuery = "";
                conn = new SqlCeConnection(ConnectionString);
                
                conn.Open();
                tr = conn.BeginTransaction();
                cmd = new SqlCeCommand("", conn, tr);
                foreach (string s in commands)
                {
                    _LastQuery = s;
                    cmd.CommandText = _LastQuery;
                    int i = cmd.ExecuteNonQuery();
                }
                _LastQuery = "";
                
            }
            catch (Exception ex)
            {
                result = false;
                throw ex;
            }
            finally
            {
                if (tr != null)
                {
                    if (result)
                    {
                        tr.Commit();
                    }
                    else
                        tr.Rollback();
                    tr.Dispose();
                }
                if (cmd != null)
                    cmd.Dispose();

                if (conn != null)
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        try
                        {
                            conn.Close();
                        }
                        catch { }
                    }
                    conn.Dispose();
                }
            }
            if(result)
                SaveCommandsToDB(commands);
            return result;
        }

        public bool SaveCommandsToDB(List<string> commands)
        {
            bool result = true;
            if (OptionCache.GetOptionBoolValue(Init.InitFormOptionNames.Main.ZapisujZmiany))
            {
                DbUpdate.DbUpdateDataSet ds = new DbUpdate.DbUpdateDataSet();
                foreach (string s in commands)
                {
                    if (s.IsNotNull())
                    ds.DB_UPDATE.AddDB_UPDATERow(this.IdPolaczenia, s.Trim().AsByteArray(), s.Trim().SubstringSafe(0, 100));
                }

                PrivateManager.AddTableDef(ds.DB_UPDATE);
                if (!PrivateManager.UpdateDataSet(ds))
                    PrivateManager.LastException.Log();
            }
            return result;
        }
    }

    public class ConnectionNodeCollection : BaseNamedCollection<ConnectionNode>, TreeList.IVirtualTreeListData
    {
        #region IVirtualTreeListData Members

        public void VirtualTreeGetCellValue(VirtualTreeGetCellValueInfo info)
        {
            
        }

        public void VirtualTreeGetChildNodes(VirtualTreeGetChildNodesInfo info)
        {
            info.Children = this;
        }

        public void VirtualTreeSetCellValue(VirtualTreeSetCellValueInfo info)
        {
        }

        #endregion
    }
    public class TableNode : INamedObject, TreeList.IVirtualTreeListData
    {
        private string _TableName;
        public string TableName
        {
            get { return _TableName; }
        }
        public TableNode(ConnectionNode node, string TableName)
        {
            _Connection = node;
            _TableName = TableName;
        }
        private List<ColumnNode> columns = new List<ColumnNode>();
        public List<ColumnNode> Columns
        {
            get
            {
                return columns;
            }
        }
        private ConnectionNode _Connection;
        public ConnectionNode Connection
        {
            get { return _Connection; }
        }

        #region IVirtualTreeListData Members

        public void VirtualTreeGetCellValue(VirtualTreeGetCellValueInfo info)
        {
            if (info.Column.FieldName == "NodeData")
            info.CellData = _TableName;
        }

        public void VirtualTreeGetChildNodes(VirtualTreeGetChildNodesInfo info)
        {
            info.Children = columns;
        }

        public void VirtualTreeSetCellValue(VirtualTreeSetCellValueInfo info)
        {
        }

        #endregion

        #region INamedObject Members

        public string Name
        {
            get
            {
                return TableName;
            }
            set
            {
                _TableName = value;
            }
        }

        #endregion
    }
    public class ColumnNode : TreeList.IVirtualTreeListData
    {
        public ColumnNode(TableNode table, string name,
            string type, int size, int precision, int scale, bool allowNulls)
        {
            _Table = table;
            _AllowNulls = allowNulls;
            _ColumnName = name;
            _ColumnType = type;
            _ColumnSize = size;
            _Precision = precision;
            _Scale = scale;
        }

        private TableNode _Table;
        public TableNode Table
        {
            get { return _Table; }
        }
        private string _ColumnName;
        private string _ColumnType;
        private bool _AllowNulls;
        private int _Precision = -1;
        private int _Scale = -1;
        private int _ColumnSize = -1;

        #region IVirtualTreeListData Members

        public void VirtualTreeGetCellValue(VirtualTreeGetCellValueInfo info)
        {
            if (info.Column.FieldName == "NodeData")
                info.CellData = _ColumnName;
            else
            {
                string s = "[" + _ColumnType;
                if (_ColumnSize > 0)
                    s += "(" + _ColumnSize + ")";
                if (_Scale > -1)
                    s += "(" + _Precision + "." + _Scale + ")";

                s += "] " + (_AllowNulls ? " NULL" : " NOT NULL");
                info.CellData = s;
            }
        }

        public void VirtualTreeGetChildNodes(VirtualTreeGetChildNodesInfo info)
        {
            
        }

        public void VirtualTreeSetCellValue(VirtualTreeSetCellValueInfo info)
        {
            
        }

        #endregion
    }
    public class TableNodeCollection : BaseNamedCollection<TableNode>
    {
        public void Sort()
        {
            TableNode n;
            for (int i = 0; i < this.Count - 1; i++)
            {
                for (int j = i; j < this.Count; j++)
                {
                    if (this[i].TableName.ToUpper().CompareTo(this[j].TableName.ToUpper()) > 0)
                    {
                        n = this[i];
                        SetItem(i, this[j]);
                        SetItem(j, n);
                    }
                }
            }
        }
    }
}
